
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[PlayerClubHistories_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[PlayerClubHistories_Insert]

IF OBJECT_ID(N'[dbo].[PlayerClubHistories_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[PlayerClubHistories_Update]

IF OBJECT_ID(N'[dbo].[PlayerClubHistories_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[PlayerClubHistories_Delete]

IF OBJECT_ID(N'[dbo].[PlayerClubHistories_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[PlayerClubHistories_Select]

--endregion

GO


--region [dbo].[PlayerClubHistories_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[PlayerClubHistories_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdPlayerClubHistory int = null,
	@IdPlayer int = null,
	@IdClub int = null,
	@StartDate datetime = null,
	@EndDate datetime = null,
	@Salary nvarchar(100) = null,
	@NumberClothe int = null,
	@Position nvarchar(100) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[PlayerClubHistories]
					WHERE
					(
						(@IdPlayerClubHistory is null OR [PlayerClubHistories].[IdPlayerClubHistory] = @IdPlayerClubHistory)
						AND (@IdPlayer is null OR [PlayerClubHistories].[IdPlayer] = @IdPlayer)
						AND (@IdClub is null OR [PlayerClubHistories].[IdClub] = @IdClub)
						AND (@StartDate is null OR [PlayerClubHistories].[StartDate] = @StartDate)
						AND (@EndDate is null OR [PlayerClubHistories].[EndDate] = @EndDate)
						AND (@Salary is null OR [PlayerClubHistories].[Salary] like @Salary)
						AND (@NumberClothe is null OR [PlayerClubHistories].[NumberClothe] = @NumberClothe)
						AND (@Position is null OR [PlayerClubHistories].[Position] like @Position)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[PlayerClubHistories].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdPlayerClubHistory' and @SortType = 1 THEN [PlayerClubHistories].[IdPlayerClubHistory] END ASC,
						CASE WHEN @SortBy = 'IdPlayerClubHistory' and @SortType = 0 THEN [PlayerClubHistories].[IdPlayerClubHistory] END DESC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 1 THEN [PlayerClubHistories].[IdPlayer] END ASC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 0 THEN [PlayerClubHistories].[IdPlayer] END DESC,
						CASE WHEN @SortBy = 'IdClub' and @SortType = 1 THEN [PlayerClubHistories].[IdClub] END ASC,
						CASE WHEN @SortBy = 'IdClub' and @SortType = 0 THEN [PlayerClubHistories].[IdClub] END DESC,
						CASE WHEN @SortBy = 'StartDate' and @SortType = 1 THEN [PlayerClubHistories].[StartDate] END ASC,
						CASE WHEN @SortBy = 'StartDate' and @SortType = 0 THEN [PlayerClubHistories].[StartDate] END DESC,
						CASE WHEN @SortBy = 'EndDate' and @SortType = 1 THEN [PlayerClubHistories].[EndDate] END ASC,
						CASE WHEN @SortBy = 'EndDate' and @SortType = 0 THEN [PlayerClubHistories].[EndDate] END DESC,
						CASE WHEN @SortBy = 'Salary' and @SortType = 1 THEN [PlayerClubHistories].[Salary] END ASC,
						CASE WHEN @SortBy = 'Salary' and @SortType = 0 THEN [PlayerClubHistories].[Salary] END DESC,
						CASE WHEN @SortBy = 'NumberClothe' and @SortType = 1 THEN [PlayerClubHistories].[NumberClothe] END ASC,
						CASE WHEN @SortBy = 'NumberClothe' and @SortType = 0 THEN [PlayerClubHistories].[NumberClothe] END DESC,
						CASE WHEN @SortBy = 'Position' and @SortType = 1 THEN [PlayerClubHistories].[Position] END ASC,
						CASE WHEN @SortBy = 'Position' and @SortType = 0 THEN [PlayerClubHistories].[Position] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[PlayerClubHistories]
			Where 
			(
				(@IdPlayerClubHistory is null OR [PlayerClubHistories].[IdPlayerClubHistory] = @IdPlayerClubHistory)
				AND (@IdPlayer is null OR [PlayerClubHistories].[IdPlayer] = @IdPlayer)
				AND (@IdClub is null OR [PlayerClubHistories].[IdClub] = @IdClub)
				AND (@StartDate is null OR [PlayerClubHistories].[StartDate] = @StartDate)
				AND (@EndDate is null OR [PlayerClubHistories].[EndDate] = @EndDate)
				AND (@Salary is null OR [PlayerClubHistories].[Salary] like @Salary)
				AND (@NumberClothe is null OR [PlayerClubHistories].[NumberClothe] = @NumberClothe)
				AND (@Position is null OR [PlayerClubHistories].[Position] like @Position)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[PlayerClubHistories_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[PlayerClubHistories_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[PlayerClubHistories_Insert]
	@IdPlayerClubHistory int OUTPUT,
	@IdPlayer int,
	@IdClub int,
	@StartDate datetime,
	@EndDate datetime,
	@Salary nvarchar(100),
	@NumberClothe int,
	@Position nvarchar(100)

AS


INSERT INTO [dbo].[PlayerClubHistories] 
(
	[IdPlayer],
	[IdClub],
	[StartDate],
	[EndDate],
	[Salary],
	[NumberClothe],
	[Position]
)
VALUES 
(
	@IdPlayer,
	@IdClub,
	@StartDate,
	@EndDate,
	@Salary,
	@NumberClothe,
	@Position
)

SET @IdPlayerClubHistory = SCOPE_IDENTITY()

--end [dbo].[PlayerClubHistories_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[PlayerClubHistories_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[PlayerClubHistories_Update]
	@IdPlayerClubHistory int,
	@IdPlayer int,
	@IdClub int,
	@StartDate datetime,
	@EndDate datetime,
	@Salary nvarchar(100),
	@NumberClothe int,
	@Position nvarchar(100)
AS


UPDATE [dbo].[PlayerClubHistories] SET
	[IdPlayer] = @IdPlayer,
	[IdClub] = @IdClub,
	[StartDate] = @StartDate,
	[EndDate] = @EndDate,
	[Salary] = @Salary,
	[NumberClothe] = @NumberClothe,
	[Position] = @Position
WHERE
	[IdPlayerClubHistory] = @IdPlayerClubHistory

--end [dbo].[PlayerClubHistories_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[PlayerClubHistories_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[PlayerClubHistories_Delete]
	@IdPlayerClubHistory int
AS


DELETE FROM [dbo].[PlayerClubHistories]
WHERE
(
	[IdPlayerClubHistory] = @IdPlayerClubHistory
)

--end [dbo].[PlayerClubHistories_Delete]
--endregion

GO
--=========================================================================================--

